﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Resources;
using System.Text.RegularExpressions;

namespace TestSystem.DataManipulation.Helpers
{
    public static class SqlHelper
    {
        private static SqlConnection connection;
     
        private static SqlConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    var connectionStringBuilder = new SqlConnectionStringBuilder();
                    connectionStringBuilder.DataSource = @"EMACHINES-E442\SQLEXPRESS";
                    connectionStringBuilder.IntegratedSecurity = true;
                    connectionStringBuilder.InitialCatalog = "TestSystem";
                    connection = new SqlConnection(connectionStringBuilder.ConnectionString);
                    connection.Open();
                }
                return connection;
            } 
        }
        private static ResourceManager resourceManager;
        private static ResourceManager ResourceManager
        {
            get
            {
                return resourceManager ??
                       (resourceManager =
                        new ResourceManager("TestSystem.DataManipulation.SqlQueries", typeof(SqlQueries).Assembly));
            }
        }
        private static readonly Regex commandInclude = new Regex(@"##(\S+)(\.sql)?##", RegexOptions.Compiled);
        private static readonly Dictionary<string, SqlCommand> resourceCommands = new Dictionary<string, SqlCommand>();

        private static void ConvertDbNulls(IEnumerable<Param> parameters)
        {
            foreach (var p in parameters.Where(p => p.Value == null))
            {
                p.Value = DBNull.Value;
            }
        }
        private static SqlCommand GetResourceCommand(string cmdName, params Param[] parameters)
		    {
			    SqlCommand cmd;
			    if (!   resourceCommands.ContainsKey(cmdName))
			    {
				    cmd = CreateTextCommand();
				    cmd.CommandText = GetCommandTextFromResource(cmdName);
				    AddParams(cmd, parameters);
				    resourceCommands[cmdName] = cmd;
			    }
			    else
			    {
				    cmd = resourceCommands[cmdName];
				    if (cmd.Parameters.Count != parameters.Length)
					    throw new ArgumentException("invalid parameters count for subsequent use");

				    ConvertDbNulls(parameters);
				    for (var i = 0; i < parameters.Length; i++)
					    cmd.Parameters[i].Value = parameters[i].Value;
			    }

			    return cmd;
		
		
   }
        private static string GetCommandTextFromResource(string cmdName)
        {
            cmdName = cmdName.Trim();
            if (cmdName.EndsWith(".sql"))
                cmdName = cmdName.Substring(0, cmdName.Length - 4);

            var commandText = ResourceManager.GetString(cmdName);
            if (String.IsNullOrEmpty(commandText))
                throw new InvalidDataException("command text is empty: " + cmdName);

            return commandInclude.Replace(commandText,
                match => GetCommandTextFromResource(match.Groups[1].Value));
        }
        private static SqlCommand CreateTextCommand()
        {
            return Connection.CreateCommand();
        }
        private static void AddParams(SqlCommand command, params Param [] list)
        {
            foreach (var parameter in list)
            {
                command.Parameters.Add(new SqlParameter
                {
                    ParameterName = parameter.Name,
                    SqlDbType = parameter.DbType,
                    Value = parameter.Value
                });
            }
        }
        public static void ExecuteNonQuery(string nme, params Param [] parameters)
        {
            var cmd = GetResourceCommand(nme, parameters);
            cmd.Prepare();
            cmd.ExecuteNonQuery();
        }
        public static SqlDataReader ExecuteAndReturnDataReader (string nme, params Param [] parameters)
        {
            var cmd = GetResourceCommand(nme, parameters);
            cmd.Prepare();
            var reader = cmd.ExecuteReader();
           
            return reader;
        }

    }
}
